Title: SQL W3S (By-BKK) / 041 Stored Procedure
Tags: Obsidian_to_Anki

Q: What is a stored procedure in SQL?
A: A stored procedure is prepared SQL code that you can save and reuse many times

Q: Why use stored procedures in SQL?
A: To avoid rewriting the same SQL query, save it as a stored procedure and call it when needed

Q: True/False: You can pass parameters to stored procedures in SQL
A: True

Q: How do you create a stored procedure in SQL Server?
A: Use CREATE PROCEDURE procedure_name AS sql_statement GO

Q: What command is used to execute a stored procedure in SQL Server?
A: EXEC procedure_name

Q: Example: Create a stored procedure that selects all records from Customers
A: CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO

Q: How do you execute a stored procedure named SelectAllCustomers?
A: EXEC SelectAllCustomers

Q: Example: Create a stored procedure with one parameter (City)
A: CREATE PROCEDURE SelectAllCustomers @City nvarchar(30) AS SELECT * FROM Customers WHERE City = @City GO

Q: How do you execute a stored procedure with one parameter (City='London')?
A: EXEC SelectAllCustomers @City = 'London'

Q: Example: Create a stored procedure with two parameters (City, PostalCode)
A: CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10) AS SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode GO

Q: How do you execute a stored procedure with two parameters (City and PostalCode)?
A: EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP'

Q: True/False: Parameters let stored procedures act on different values passed by the user
A: True

Q: Stored procedures can have multiple parameters
A: Yes. List each parameter and its data type separated by commas

Q: To save reusable SQL code for repeated use, what should you use?
A: Stored procedures

Q: A stored procedure is prepared SQL code that can be reused. What is this called in SQL Server?
A: Stored procedure

Q: You use EXEC procedure_name to execute a stored procedure. What does EXEC do?
A: EXEC runs the stored procedure

Q: Reverse card:
You use EXEC to run a stored procedure
A: How do you execute a stored procedure in SQL Server?

Q: Reverse card:
Prepared SQL code saved for repeated use is called a stored procedure
A: What is the term for saved, reusable SQL code in SQL Server?